set more off
local a 33
local y 1991

*** OUTCOMES FROM FLAT FILE FOR AGE 33
use "Data/NCDS/UKDA-5567-stata11_se/stata11_se/ncds5cmi.dta", clear

rename ncdsid NCDSID

* merge RPI
ge year=`y'
merge m:1 year using scratchdata/RPI.dta, keep(match) nogenerate

** sex
rename n502615 sex_`a'

** Observation indicator
ge Obs_33=1

rename n5region region_`a'

********************************************************************************
* Part 1: pay period for employed
********************************************************************************

* net (current/most recent job, usual)
ge pp_nee=n500542 if n500542>0 
recode pp_nee (3=4) (4=4.33) (5=52) (6=.)

* gross (current/most recent job, usual)
ge pp_gee=n500549  if n500549>0 
recode pp_gee (3=4) (4=4.33) (5=52) (6=.)

* last net pay (current/most recent job, asked if no usual pay)
ge pp_neel=n500559 if n500559>0  
recode pp_neel (3=4) (4=4.33) (5=52) (6=.)

* last gross pay (current/most recent job, asked if no usual pay)
ge pp_geel=n500566 if n500566>0  
recode pp_geel (3=4) (4=4.33) (5=52) (6=.)

********************************************************************************
* Part 2: pay for employed
********************************************************************************

* net 
ge  npee_`a'=n500536
recode  npee_`a' (999996=.) (999999=.)

* gross
ge gpee_`a'=n500543
recode  gpee_`a' (999999=.)

* last net
ge lnpee_`a'=n500553
recode lnpee_`a' (999999=.)

* last gross
ge lgpee_`a'=n500560 
recode lgpee_`a' (999999=.)

********************************************************************************
* Part 3: pay per week for employed
********************************************************************************

* net usual 
replace npee_`a'=npee_`a'/pp_nee 

* gross usual
replace gpee_`a'=gpee_`a'/pp_gee 

* last net
replace lnpee_`a'=lnpee_`a'/pp_neel 

* last gross
replace lgpee_`a'=lgpee_`a'/pp_geel 

********************************************************************************
* Part 4: hours per week for employed
********************************************************************************

* hours for usual pay 
ge hoursee_`a'=n500550

* hours for last pay
ge hourseel_`a'=n500567

********************************************************************************
* Part 5: weekly earnings for self-employed
********************************************************************************


ge ese_`a'=n500718 if n500718>1 
ge gp_ll=.
ge gp_ul=.
replace gp_ll=0 if ese_`a'==2 
replace gp_ll=50 if ese_`a'==3 
replace gp_ll=100 if ese_`a'==4 
replace gp_ll=200 if ese_`a'==5 
replace gp_ll=300 if ese_`a'==6 
replace gp_ll=500 if ese_`a'==7 
replace gp_ll=1000 if ese_`a'==8 

replace gp_ul=50 if ese_`a'==2 
replace gp_ul=100 if ese_`a'==3
replace gp_ul=200 if ese_`a'==4 
replace gp_ul=300 if ese_`a'==5
replace gp_ul=500 if ese_`a'==6 
replace gp_ul=1000 if ese_`a'==7 

intreg gp_ll gp_ul
predict p if gp_ll!=., ystar(gp_ll,gp_ul)
replace ese_`a'=p
replace ese_`a'=0 if n500718==1
drop p

ge npse_`a'=.  /* profit information not collected*/

********************************************************************************
* Part 6: hours per week for self-employed
********************************************************************************

ge hoursse_`a'=. /* information not collected*/

********************************************************************************
* cleanup: replace usual with last if usual take home missing and last not missing
********************************************************************************

ge rep=1 if npee_`a'==. & lnpee_`a'!=.

replace hoursee_`a'=hourseel_`a' if rep==1 & hourseel_`a'!=.
drop hourseel_`a'

replace npee_`a'=lnpee_`a' if rep==1
drop lnpee_`a'

replace gpee_`a'=lgpee_`a' if rep==1 & lgpee_`a'!=.
drop lgpee_`a'

drop rep

********************************************************************************
* Part 7: inflation adjustment
********************************************************************************

local vars npee_`a' gpee_`a' ese_`a' npse_`a'
foreach v of local vars {
replace `v'=`v'/rpi
}

********************************************************************************
* Part 8: Economic activity
********************************************************************************

rename n500520 EconAct
replace EconAct=. if EconAct<0

ge EE_`a'=(EconAct==1|EconAct==2) if EconAct!=. 
ge SE_`a'=(EconAct==3|EconAct==4) if EconAct!=. 

* clasify as non-working if report zero earnings
replace EE_`a'=0 if npee_`a'==0 & EE_`a'!=.
replace SE_`a'=0 if ese_`a'==0  & SE_`a'!=.
ge AE_`a'=EE_`a'+SE_`a'

ge UN_`a'=EconAct==5 if EconAct!=.

********************************************************************************
* Part 9: recode hours
********************************************************************************

* recode hours to missing if hours>112 or zero
replace hoursee_`a'=. if (hoursee_`a'>112|hoursee_`a'==0)
replace hoursse_`a'=. if (hoursse_`a'>112|hoursse_`a'==0)

replace hoursee_`a'=40 if EconAct==1 & hoursee_`a'==.
replace hoursee_`a'=20 if EconAct==2 & hoursee_`a'==.
replace hoursse_`a'=40 if EconAct==3 & hoursse_`a'==.
replace hoursse_`a'=20 if EconAct==4 & hoursse_`a'==.

replace hoursee_`a'=. if  npee_`a'==.
replace hoursse_`a'=. if  ese_`a'==.

* recode earnings and hours as missing if don't match current economic activity
replace npee_`a'=. if EE_`a'!=1
replace gpee_`a'=. if EE_`a'!=1
replace hoursee_`a'=. if  EE_`a'!=1
replace ese_`a'=. if SE_`a'!=1
replace hoursse_`a'=. if SE_`a'!=1

********************************************************************************
* Part 10: hourly wages
********************************************************************************

* employed net
ge wageee_`a'=npee_`a'/hoursee_`a'

* employed gross
ge gwageee_`a'=gpee_`a'/hoursee_`a'

* self-employed (earnings)
ge wagese_`a'=ese_`a'/hoursse_`a'

* self-employed (profit) 
ge prose_`a'=npse_`a'/hoursse_`a'

********************************************************************************
* Part 11: combine employed and self-employed
********************************************************************************

egen wage_`a'=rowmax(wageee_`a' wagese_`a')
drop wageee_`a' wagese_`a'

egen pay_`a'=rowmax(npee_`a' ese_`a')
drop npee_`a' ese_`a'

egen hours_`a'=rowmax(hoursee_`a' hoursse_`a')
drop hoursee_`a' hoursse_`a'

********************************************************************************
*  Part 12: log variables
********************************************************************************

local vars wage pay hours
foreach v of local vars {
ge l`v'_`a'=ln(`v'_`a')
}

********************************************************************************
* Part 13: occupation
********************************************************************************

* SOC90
ge soc90_`a'=n540014
recode soc90_`a' (8=800)
replace soc90_`a'=soc90_`a'*10 if soc90_`a'>=10 & soc90_`a'<=99
replace soc90_`a'=. if soc90_`a'<0
replace soc90_`a'=. if AE_`a'!=1


********************************************************************************
* Part 14: Occupational attainment
********************************************************************************

rename n540033 SC_`a'
replace SC_`a'=. if SC_`a'==0|AE_`a'!=1
replace SC_`a'= SC_`a'/10


********************************************************************************
* Part 15: Job security
********************************************************************************

ge JobSecurity_`a'=.

********************************************************************************
* Work at 33
********************************************************************************

label define Iwork_`a' 1 "Not at all true" 2 "Somewhat untrue" 3 "Neither true/untrue" 4 "Somewhat true" 5 "Very true"

foreach j in 13 14 15 16 17{
rename n5096`j' Iwork`j'_`a'
replace Iwork`j'_`a'=. if AE_`a'!=1
replace  Iwork`j'_`a'=6-Iwork`j'_`a'
label values Iwork`j'_`a' Iwork_`a'
}

********************************************************************************
* Skills at 33 (self evaluations)
********************************************************************************

label define SkE33 1 "Don't have skill" 2 "Poor" 3 "Fair" 4 "Good", replace
forvalues i=1/15{
local j=18+3*(`i'-1)
ge SkE`j'_`a'=n5096`j' if n5096`j'!=.
replace SkE`j'_`a'=5-SkE`j'_`a'
label values SkE`j'_`a' SkE33
su SkE`j'_`a'
replace SkE`j'_`a' = (SkE`j'_`a'-r(mean))/r(sd)
}


********************************************************************************
* Finish up
********************************************************************************

keep NCDSID *_`a'
sort NCDSID
rename *_`a' *_I`a'

save scratchdata/ncds_1991_flatfile.dta, replace

/*
********************************************************************************
* Part 17: education at age 23 (1981)
********************************************************************************

ge AttQual=1 if n501513!=.

* O levels
egen OLevels_`a'=anymatch(n501513-n501541), values(2 3 6)
replace OLevels_`a'=. if AttQual!=1

* A levels
egen ALevels_`a'=anymatch(n501513-n501541), values(5 8 9)
replace ALevels_`a'=. if AttQual!=1

* vocational level 2 qualifications (RSA, C&G, OND, ONC)
egen Voc2_`a'=anymatch(n501513-n501541), values(10 11 12 13 14 15 16 17 18 19 20 21)
replace Voc2_`a'=. if AttQual!=1

* Non-degree higher qual  
egen HQual_`a'=anymatch(n501513-n501541), values(22 23 24 25 26 27 28 29 30)
replace HQual_`a'=. if AttQual!=1

* Degree
egen Degree_`a'=anymatch(n501513-n501541), values(31 32 33)
replace Degree_`a'=. if AttQual!=1

* Highest educational qualification by 23
ge HiQual_`a'=hqual23
recode HiQual_`a' (-1 = .)

ge SC_NM_`a'=1 if SC_`a'>=1 & SC_`a'<=3.15
replace SC_NM_`a'=0 if SC_NM_`a'==. & SC_`a'!=.

ge SC_ProHMT_`a'=1 if SC_`a'>=1 & SC_`a'<=2
replace SC_ProHMT_`a'=0 if SC_ProHMT_`a'==. & SC_`a'!=.
